# -*_ codeing=utf-8 -*-
# @Time: 2023/1/11 20:34
# @Author: foxhuty
# @File: ec_dict.py
# @Software: PyCharm
# @Based on python 3.10
import pandas as pd
from sqlalchemy import create_engine
import mysql.connector


def title_lines(f):
    def wrapper(*args, **kwargs):
        print("{0:#^55}".format("欢迎使用Huty简易英汉词典"))
        res = f(*args, **kwargs)
        print("{0:#^55}".format("程序结束，谢谢使用"))
        return res

    return wrapper


def create_conn_cursor():
    conn = mysql.connector.connect(host='localhost', user='root',
                                   passwd='foxmmer', database='db_ec',
                                   auth_plugin='mysql_native_password')
    cursor = conn.cursor()
    return conn, cursor


def read_into_mysql(file_path, tb_name, db_name='db_ec'):
    engine = create_engine(f"mysql+mysqlconnector://root:foxmmer@localhost:3306/{db_name}?charset=utf8")
    conn = engine.connect()
    df = pd.read_csv(file_path, encoding='utf8', low_memory=False)
    df = df[['word', 'phonetic', 'definition', 'translation']]
    print(df.shape)
    df.index.name = 'id'
    df.to_sql(name=tb_name, con=conn, if_exists='replace')
    conn.close()
    print('successfully saved to mysql')


def save_into_mysql(data):
    conn, cursor = create_conn_cursor()
    sql = "insert into tb_word_checked (word,english_definition,chinese_translation)values(%s,%s,%s)"
    val = (data[1], data[3], data[4])
    query_sql = f"select word from tb_word_checked where word='{data[1]}'"
    cursor.execute(query_sql)
    res = cursor.fetchone()
    if res is None:
        cursor.execute(sql, val)
        conn.commit()
        cursor.close()
        conn.close()


def create_table():
    conn, cursor = create_conn_cursor()
    sql = """
    create table tb_word_checked (
    id int auto_increment primary key,
    word varchar(20) not null,
    english_definition varchar(1000),
    chinese_translation varchar(1000)
    )
    """
    cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()


@title_lines
def query_word():
    """"
    自制简易英汉词典，带音标，中英文解释。词库内容极其庞大，应有尽有！
    """
    conn, cursor = create_conn_cursor()
    while True:
        word_query = input('请输入单词：')
        if len(word_query) == 0:
            break
        sql = f"select * from tb_ec_dict where word='{word_query}'"
        cursor.execute(sql)
        result = cursor.fetchone()
        if result is not None:
            # 把第一次查找的单词存入数据库，不重复存入
            save_into_mysql(result)
            print("{0:+^58}".format('单词信息如下'))
            print(f'目标单词：{result[1]}\n单词读音：[{result[2]}]')
            print('英语解释:')
            try:
                if len(result[3]) > 0:
                    en_ex = result[3].split('\\n')
                    n = 1
                    for i in en_ex:
                        print(n, end=',')
                        print(i)
                        n += 1
            except:
                print(result[3])
            print('中文翻译:')
            try:
                if len(result[4]) > 0:
                    cn_ex = result[4].split('\\n')
                    n = 1
                    for i in cn_ex:
                        print(n, end=',')
                        print(i)
                        n += 1
            except:
                print(result[4])
            print("{0:+^60}".format('按enter键结束'))
        else:
            print("{0:*^49}".format('查无此单词或短语，请检查拼写是否正确！！！'))
    cursor.close()
    conn.close()


if __name__ == '__main__':

    # create_table()
    query_word()
